Analyzing the 2013 AP CS Test Report and the 2015 Income Census Data

R Configuration

Below we display our sessionInfo().

sessionInfo(package=NULL)

R version 3.4.0 (2017-04-21)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS Sierra 10.12.4

Matrix products: default
BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/3.4/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] grid      stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] readr_1.1.0           readxl_1.0.0          reshape2_1.4.2        choroplethrMaps_1.0.1
 [5] choroplethr_2.2.0     acs_2.0               XML_3.98-1.6          plyr_1.8.4           
 [9] stringr_1.2.0         RColorBrewer_1.1-2    gridExtra_2.2.1       data.world_0.1.2     
[13] dplyr_0.5.0           ggplot2_2.2.1         DT_0.2                shinydashboard_0.5.3 
[17] shiny_1.0.3          

loaded via a namespace (and not attached):
 [1] splines_3.4.0       lattice_0.20-35     WDI_2.4             colorspace_1.3-2    htmltools_0.3.6    
 [6] yaml_2.1.14         base64enc_0.1-3     survival_2.41-3     foreign_0.8-68      DBI_0.6-1          
[11] cellranger_1.1.0    munsell_0.4.3       gtable_0.2.0        htmlwidgets_0.8     evaluate_0.10      
[16] latticeExtra_0.6-28 knitr_1.15.1        httpuv_1.3.3        curl_2.5            htmlTable_1.9      
[21] Rcpp_0.12.10        acepack_1.4.1       xtable_1.8-2        scales_0.4.1        backports_1.0.5    
[26] checkmate_1.8.2     Hmisc_4.0-2         jsonlite_1.4        mime_0.5            hms_0.3            
[31] digest_0.6.12       stringi_1.1.5       rprojroot_1.2       tools_3.4.0         bitops_1.0-6       
[36] magrittr_1.5        lazyeval_0.2.0      RCurl_1.95-4.8      tibble_1.3.0        Formula_1.2-1      
[41] cluster_2.0.6       Matrix_1.2-10       rsconnect_0.7       data.table_1.10.4   httr_1.2.1         
[46] assertthat_0.2.0    rmarkdown_1.4       R6_2.2.0            rpart_4.1-11        nnet_7.3-12        
[51] compiler_3.4.0     

Our Datasets

The first dataset was created by students at Georgia Tech University. It focuses on the demographics of test-takers for the AP CS test administered by the Collegeboard in 2013. The lowest score for an AP test is a 1, while the highest score is a 5. The second dataset that we used to join the AP dataset contains census data related to income in the U.S in 2015. Download AP dataset here. Download the 2015 US Census Income data US Census population estimates, Income (American Community Survey, 2011-2015)

AP Test Dataset Columns

Measure	Description
2013 data	The U.S. States
# schools	Number of school that offer AP CS tests
Total #	Number of AP CS test takers
Yield per teacher	Number of student test takers per teacher
# passed	The number of test takers who scored 3 out of 5 or above
% passed	The percentage of passed test takers over the total number of test takers
# female	The number of female test taker
# female passed	The number of female test taker who scored 3 out of 5 or above
% female passed	The percentage of passed female test takers over the total number of female test takers
% female	The percentage of female test takers over the total number of test takers
# Black	The number of black test taker
# Black Passed	The number of black test taker who scored 3 out of 5 or above
% Black passed	The percentage of passed black test takers over the total number of test takers
% black in state	Percentage of black in state
# Hispanic	The number of Hispanic test taker
# Hispanic passed	The number of Hispanic test taker who scored 3 out of 5 or above
% Hispanic passed	The percentage of passed Hispanic test takers over the total number of test takers
% Hispanic in state	Percentage of Hispanics in state
% taking / % state * 100	Attempt rate of Hispanic test takers

Census Dataset Columns

Measure	Description
State	State abbreviation
AreaName	State full name
median_household_income	Median household income in 2015
per_capita_income	A measurement an area’s average income
gini_index	A measurement of inequality. (0 stands for perfect equality)

Methodology

Original Dataset

The 2013 AP CS test dataset before the cleaning process

ETL Logic

1. As seen from the previous table, the uncleaned AP CS data is filled up with empty columns, asterisks and multiple columns that we will not use in our data visualization. We followed the following process to clean up the data:

   i. Build an ETL script to pull the data from data.world and proceed the cleaning process 

### Load Required Packages
require(readxl)
# Set the working directory to access CSVs
setwd("~/s17dvfinalproject-dvproject-4-chou-zeng-zhang-zhang/01 Data")

# df <- read_excel("./01 Data/DetailedStateInfoAP-CS-A-2006-2013-with-PercentBlackAndHIspanicByState-fixed.xlsx", sheet = "Sheet2")
df <- read_excel("../01 Data/DetailedStateInfoAP-CS-A-2006-2013-with-PercentBlackAndHIspanicByState-fixed.xlsx", sheet = "Sheet2")

   ii. Remove empty column from dataframe.

# Remove empty column from dataframe
df <- subset(df, select = -c(X__1))

   iii. Remove last 4 rows of dataframe.

# Remove last 4 rows of dataframe
n <- dim(df)[1]
df <- df[1:(n-4), ]

   iv. Remove asterisks (*) from dataframe and replace them with ""

# Remove asterisks (*) from dataframe and replace with ""
df <- lapply(df, function(x) {
  gsub("\\*", "", x)
})

   v. Replace any record that is "" with NA.

# Replace any record that is "" with NA
df[df == ""] <- NA

   vi. Rename columns of dataframe for clarity and simplicity.

# Rename columns of dataframe
colnames(df) <- c("state", "number_of_schools", "total_takers", "yield_per_teacher", 
                  "total_passed", "percent_passed", 
                  "total_female","total_female_passed", "percent_female_passed", "percent_female_taking", 
                  "total_black", "total_black_passed", "percent_black_passed", "percent_black_taking", "percent_black_state", "attempt_rate_black", 
                  "total_black_females", "total_black_females_passed", "percent_black_females_passed", 
                  "total_hispanic", "total_hispanic_passed", "percent_hispanic_passed", 
                  "total_hispanic_female", "total_hispanic_female_passed", "percent_hispanic_female_passed", "percent_hispanic_taking", "percent_hispanic_state", "attempt_rate_hispanic")

   vii. Generate a cleaned up dataset called "ap_cs_2013_states_clean.csv"

# Write finished dataframe to csv file
write.csv(df, file = "ap_cs_2013_states_clean.csv", row.names=FALSE, na="")

2. In order to join the AP CS 2013 dataset to the 2015 Income Census data, we ran a query on data.world in order to rename the columns for simplicity, and we downloaded it as “acs-2015-5-e-income-queried.csv”.
3. Use Tableau’s web data connector to import the datasets. Join the two datasets together by State to merge the datasets in order for us to explore the relationship between two datasets.
4. We had to set the “S17 DV Project 6” data.world datasets PUBLIC in order to access and work on it in RStudio when we wrote the server code.
5. Use SQL and R SDK in order to create the similar and other visualizations with ggplot.
6. Create the Shiny App which includes all of the different types of visualizations, and publish it onto the Shiny server.

Cleaned Dataset

This is the AP CS Test dataset after the cleaning process:

Visualizations

Visualizations were created using both Tableau and R Shiny.

Box Plots

Insight for Figure 1: Yield Per Teacher by Region
These boxplots compare the distributions of test takers per teacher between each region. The distributions are not drastically different overall. Their spreads are rather similar, and it seems that the median ratio hovers around 10 test takers per teacher.

It seems as though the Northeast has a lower test taker to teacher ratio than the other regions. The South certainly has the most variation in its distribution, as well as possessing the highest median test taker to teacher ratio. All but one distribution contain no outliers. The West region contains two, with each being at opposite extremes of the test taker per teacher metric.

This boxplot displays the distribution of yield per teacher in each region. For West region, the points are relatively concentrated however there is an outlier(California).

Histogram

Insight for Figure 2: Score Distributions for Coastal and Landlocked States
We wanted to see if there was a significant difference in the distributions in the AP score counts between US states that are geographically coastal and those that are geographically landlocked. We noticed that the distributions are almost identical, suggesting that the geography of test takers has little to do with the AP score outcomes. Also, both distributions are bimodal and almost symmetric.

Scatter Plots

Insight for Figure 3 - 6: Female Pass Rate Related to Student Teacher Ratio by Region
This scatterplot demonstrates the relationship between the Exam pass rate for both Female,Male and the number of exam takers per teacher. We made scatterplot for each region, Midwest, Northeast, South and West. The line in each page is the trend line for the rate in each region.

In Midwest and Northeast, the pass rates tend to decrease as the number of students each teacher has increases. For South region, the female pass rate is tend to decrease however male pass rate tend to increase with number of students per teacher going up. Interestingly, in West region, with larger number of students per teacher, the pass rate for both female and male would be higher.

Cross Tabs

Insight for Figure 7: AP Score by States Colored by Passed rate level
This crosstab shows the number of people in each score category for individual states colored by KPI passed rate level. Passed rate between 0 and 60 percent is considered as low passed rate. Rate between 60 and 75 percent is set to be medium pass rate. Passed rate that is higher than 75 percent is considered high.California and Texas are the two states with largest number of AP CS exam takers and score 5 people.

AP Score by States Colored by Passed rate level Filtered by High per capita income states We first select per capita income and states columns to make a bar-chart, pick 9 states with the highest per capita income and create a high per capita income states set. The crosstab shows the number of people in each score category for each of the 9 states. The KPI is created using passed rate level parameter. With a relatively high per capita income, these states all have a medium to high passed rate on AP CS exam.

Barcharts

Insight for Figure 8: Taking rate for Black, Hispanic and other in high income states.
First create a barcharts by selecting median household income and Area name(state) columns from the income census data. Select the 12 highest median household income states and create a high income states sets.

This barchart displays the percentage of total test takers from Black, Hispanic and other race groups in each state, for states with a median household income greater than $60,000.In each state, Blacks and Hispanics exam takers are way less than other race groups. We noticed that Maryland had the largest proportion of Black test takers. California, which is along the border with Mexico, has a relatively higher percentage of Hispanic test takers.Lastly, Alaska and Utah had no blacks or hispanics that took the test, but this is most likely due to the fact that they have relatively low percentages of black and hispanic residents

Insight for Figure 9: Passed rate for Male and Female in high passed rate states
First create a barchart by selecting female and male percent passed and states columns from AP CS states data. Create a high household income states sets by setting the lower bound of high median household income as 60000.

This barchart displays the different passed rates for male and female in each of these 12 states that have the highest median household income. In each of the 12 high income states, males generally outperform females. The reference line displays the average passed rate for all 50 states. For these high income states except for Utah and Hawaii, male passed rate exceeds the overall average rate. However, Female passed rates are generally below the average.

Maps

Insight for Figure 10: Map of Income Inequality in the U.S.
Using the census data, we created a choropleth map displaying the Gini index in the US. This map was created using Ari Lamstein’s Choroplethr and Ezra Haber Glenn’s ACS packages. The classes are created using the Jenks natural breaks classification method.

The Gini index is a normalized measure of income inequality. We observed that many coastal states have generally higher index values than landlocked states, which compelled us to examine the AP CS score distributions between coastal and landlocked states, as seen in the histogram tab.

Insight for Figure 11: Map of Taking Rate for Hispanic
This map shows the Hispanic taking rate in each states. Percent Hispanic taking is the number of Hispanic exam takers as a percentage of total number of takers in this state. Colored and Labeled with the percent Hispanic taking. Darker Blue means a higher Hispanic taking rate, lighter color means low Hispanic taking percentage. South region has a relatively high Hispanic taking rate.

Insight for Figure 12: Map of Taking Rate for Blacks
This map shows the Black taking rate in each states. Percent Black taking is the number of Black exam takers as a percentage of total number of takers in this state. Colored and Labeled with the percent black taking. Darker orange means a higher Black taking rate, lighter color means low Black taking percentage. Southeast region has a relatively high Black taking rate.

Shiny App

To view our Shiny web app we made for the project, please click the image below. It contains other interesting visualizations that are not shown in this notebook.